/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package idea.model.dbmanager;


import idea.model.qnrlibrary.CustomContentItems;
import idea.model.qnrlibrary.CustomContentLayouts;
import idea.model.qnrlibrary.CustomResponseDeView;
import idea.model.qnrlibrary.QuestionTitle;
import idea.model.qnrlibrary.QuestionTitleLinkView;
import idea.model.qnrlibrary.Questionnaires;
import idea.model.qnrlibrary.ReportingPeriods;
import java.awt.Image;
import java.io.IOException;
import java.io.Reader;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.swing.ImageIcon;
import org.openide.util.Exceptions;


/**
 * Builds and runs all Java Persistence queries
 *
 * @author rejun001
 */
public class QueryManager
{

    private static EntityManager entityManager = Persistence.createEntityManagerFactory( "QnrLibraryPU" ).createEntityManager();
    private Clob clob = null;
    private Blob jpegBlob = null;
    private String clobStr = null;
    private static EntityTransaction transaction = null;


    /**
     * xtor.
     */
    public QueryManager()
    {
    }


    /**
     *
     * @return a list of all surveys in questionnaires, to populate ComboBox.
     * Strings.
     */
    public static List<String> getAllSurveys()
    {
        Query query = entityManager.createQuery( "SELECT DISTINCT q.survey FROM Questionnaires q ORDER BY q.survey ASC" );
        return query.getResultList();
    }


    /**
     *
     * @param survey
     * @return a list of all Ref Periods for a given survey, to populate
     * ComboBox. Strings.
     */
    public static List<String> getRefPeriods( String survey )
    {
        return entityManager.createQuery(
                "SELECT DISTINCT q.refPeriod "
                + "FROM Questionnaires q "
                + "WHERE q.survey = :survey " )
                .setParameter( "survey" , survey )
                .getResultList();
    }


    /**
     *
     * @param survey
     * @param refPeriod -- a ReportingPeriods (this is a join under the covers)
     * @return a list of all Form Numbers for a given survey and
     * ReportingPeriods' ref period, to populate ComboBox. Strings.
     */
    public static List<String> getFormNumbers( String survey , ReportingPeriods refPeriod )
    {
        return entityManager.createQuery(
                "SELECT DISTINCT q.formNumber "
                + "FROM Questionnaires q "
                + "WHERE q.survey = :survey AND q.refPeriod = :refPeriod ORDER BY q.formNumber" )
                .setParameter( "survey" , survey )
                .setParameter( "refPeriod" , refPeriod )
                .getResultList();
    }


    /**
     *
     * @param survey
     * @param refPeriod
     * @return a list of all Form Numbers for a given survey and ref period
     * (String). Strings.
     */
    public static List<String> getFormNumbers( String survey , String refPeriod )
    {
        return entityManager.createQuery(
                "SELECT DISTINCT q.formNumber "
                + "FROM Questionnaires q "
                + "WHERE q.survey = :survey AND q.refPeriod = :refPeriod ORDER BY q.formNumber" )
                .setParameter( "survey" , survey )
                .setParameter( "refPeriod" , refPeriod )
                .getResultList();
    }


    /**
     *
     * @param qnrId
     * @return a list of all QuestionTitleLinkView objects to display in a table
     * for a given Questionnaire.
     */
    public static List<QuestionTitleLinkView> getQuestionTitles( String qnrId )
    {
        return entityManager.createQuery(
                "SELECT q FROM QuestionTitleLinkView q "
                + "WHERE q.qnrId = :qnrId ORDER BY q.questSequence" )
                .setParameter( "qnrId" , qnrId )
                .getResultList();
    }


    /**
     * NOT USED -- see below
     *
     * @param questId -- a string
     * @return a list of all CustomContentItems objects to display in a table
     * for a given Question.
     */
    public static List<CustomContentItems> getCustomContentItems( String questId )
    {
        // needs QuestionTitle not String.
        return entityManager.createQuery(
                "SELECT c FROM CustomContentItems c, QuestionTitle qt "
                + "WHERE qt.questId = :questId "
                + "AND c.questId = qt.questId ORDER BY c.itemSequence" )
                .setParameter( "questId" , questId )
                .getResultList();
    }


    /**
     * Get the QuestionTitle object associated with this questId
     *
     * @param questId
     * @return a list of QuestionTitle objects. Should be only one.
     */
    public static List<QuestionTitle> getQT( String questId )
    {
        return entityManager.createQuery(
                "SELECT qt FROM QuestionTitle qt "
                + "WHERE qt.questId = :questId" )
                .setParameter( "questId" , questId )
                .getResultList();
    }


    /**
     *
     * @param questId -- the QuestionTitle the Id points to
     * @return a list of all CustomContentItems objects to display in a table
     * for a given Question.
     */
    public static List<CustomContentItems> getCustomContentItems( QuestionTitle questId )
    {
        return entityManager.createQuery(
                "SELECT c FROM CustomContentItems c "
                + "WHERE c.questId = :questId ORDER BY c.itemSequence" )
                .setParameter( "questId" , questId )
                .getResultList();
    }


    /**
     *
     * @param itemId -- a string
     * @return a list of all CustomResponse Data Element Views to display in a
     * table for a given Custom Content Item.
     */
    public static List<CustomResponseDeView> getDataElements( String itemId )
    {
        return entityManager.createQuery(
                "SELECT d FROM CustomResponseDeView d "
                + "WHERE d.itemId = :itemId ORDER BY d.occurrenceSequence" )
                .setParameter( "itemId" , itemId )
                .getResultList();
    }


    /**
     *
     * @param questId
     * @return the CustomContentLayouts object for the given questId.
     */
    public static List<CustomContentLayouts> getCustomContentLayouts( QuestionTitle questId )
    {
        return entityManager.createQuery(
                "SELECT c FROM CustomContentLayouts c "
                + "WHERE c.questId = :questId AND "
                + " c.formMedia = 'PAPER'" )
                .setParameter( "questId" , questId )
                .getResultList();
    }


    /**
     * Get Designer SFO
     */
    /**
     * Build the questionnaire query for the criteria chosen in the
     * OpeningEditorTopComponent based on the parms (from text fields)
     *
     * @param survey
     * @param title
     * @param subTitle
     * @param formNumber
     * @param refPeriod
     * @param createdBy
     * @return a list of Questionnaires object results
     */
    public static List<Questionnaires> buildQnrQuery( String survey , String title , String subTitle , String formNumber , String refPeriod , String createdBy )
    {
        Query myQuery;
        final String OPEN_BRACKET = "[";
        boolean useRPs = false;   // unlikely: using a ReportingPeriods reference in the Questionnaires table instead of a regular String

        // if no selected fields, return everything
        if ( ( survey == null || survey.isEmpty() || survey.startsWith( OPEN_BRACKET ) )
                && ( subTitle == null || subTitle.isEmpty() || subTitle.startsWith( OPEN_BRACKET ) )
                && ( title == null || title.isEmpty() || title.startsWith( OPEN_BRACKET ) )
                && ( refPeriod == null || refPeriod.isEmpty() || refPeriod.startsWith( OPEN_BRACKET ) )
                && ( createdBy == null || createdBy.isEmpty() || createdBy.startsWith( OPEN_BRACKET ) )
                && ( formNumber == null || formNumber.isEmpty() || formNumber.startsWith( OPEN_BRACKET ) ) )
        {
            myQuery = entityManager.createNamedQuery( "Questionnaires.findAll" );
        } else
        {
            // build the query
            StringBuilder sb = new StringBuilder( "SELECT q FROM Questionnaires q" );

            String where = " WHERE";
            String and = " AND";
            String conj = where;

            boolean hasSurvey = false;
            boolean hasTitle = false;
            boolean hasSubTitle = false;
            boolean hasCreatedBy = false;
            boolean hasFormNumber = false;
            boolean hasRefPeriod = false;

            String addSurvey = "";
            String addTitle = "";
            String addSubtitle = "";
            String addCreatedBy = "";
            String addFormNum = "";
            String addRefPeriod = "";

            if ( !survey.isEmpty() )
            {
                survey = addWildcards( survey );
                addSurvey = conj + " q.survey LIKE :survey";
                conj = and;
                hasSurvey = true;
            }
            if ( !subTitle.isEmpty() )
            {
                subTitle = addWildcards( subTitle );
                addSubtitle = conj + " q.subTitle LIKE :subTitle";
                conj = and;
                hasSubTitle = true;
            }
            if ( !title.isEmpty() )
            {
                title = addWildcards( title );
                addTitle = conj + " q.title LIKE :title";
                conj = and;
                hasTitle = true;
            }
            if ( !createdBy.isEmpty() )
            {
                createdBy = addWildcards( createdBy );
                addCreatedBy = conj + " q.createdBy LIKE :createdBy";
                conj = and;
                hasCreatedBy = true;
            }
            if ( !refPeriod.isEmpty() )
            {

                refPeriod = addWildcards( refPeriod );
                if ( useRPs )
                {
                    // Note that this one is getting refPeriod from a ReportingPeriods object called refPeriod,
                    //  not a direct String.
                    addRefPeriod = conj + " q.refPeriod.refPeriod LIKE :refPeriod";
                } else
                {
                    addRefPeriod = conj + " q.refPeriod LIKE :refPeriod";
                }
                conj = and;
                hasRefPeriod = true;
            }
            if ( !formNumber.isEmpty() )
            {
                formNumber = addWildcards( formNumber );
                addFormNum = conj + " q.formNumber LIKE :formNumber";
                //conj = and;
                hasFormNumber = true;
            }
            sb.append( addSurvey );
            sb.append( addSubtitle );
            sb.append( addTitle );
            sb.append( addFormNum );
            sb.append( addRefPeriod );
            sb.append( addCreatedBy );
            String query = sb.toString();

            // create the Entity Manager Query
            myQuery = entityManager.createQuery( query );
            if ( hasSurvey )
            {
                myQuery = myQuery.setParameter( "survey" , survey );
            }
            if ( hasTitle )
            {
                myQuery = myQuery.setParameter( "title" , title );
            }
            if ( hasSubTitle )
            {
                myQuery = myQuery.setParameter( "subTitle" , subTitle );
            }
            if ( hasFormNumber )
            {
                myQuery = myQuery.setParameter( "formNumber" , formNumber );
            }
            if ( hasRefPeriod )
            {
                myQuery = myQuery.setParameter( "refPeriod" , refPeriod );
            }
            if ( hasCreatedBy )
            {
                myQuery = myQuery.setParameter( "createdBy" , createdBy );
            }
        }
        // and run it
        return myQuery.getResultList();
    }


    /**
     * Add % on either side so that a LIKE search is automatic
     *
     * @param s
     * @return the string surrounded by % wildcards
     */
    private static String addWildcards( String s )
    {
        if ( !s.startsWith( "%" ) )
        {
            s = "%" + s;
        }
        if ( !s.endsWith( "%" ) )
        {
            s = s + "%";
        }
        return s;
    }


    /**
     * Get an SFO clob (encoded, compressed) from the CustomContentLayouts
     * table, one of three SFO types
     *
     * @param type which SFO type it is
     * @param questId The associated QuestionTitle
     * @return length of retrieved clob. Side effect: populate the clobStr
     * object.
     */
    public long getClobFromDB( SfoEnum type , QuestionTitle questId )
    {
        long clobLen = 0;
        String col = null;
        switch ( type )
        {
            case LAYOUT:
                col = "itemLayout";
                break;
            case DESIGNER:
                col = "designerSfo";
                break;
            case ENCAPSULATED:
                col = "encapsulatedSfo";
                break;
            default:
                break;
        }

        List<String> ccls = entityManager.createQuery(
                "SELECT c." + col + " FROM CustomContentLayouts c "
                + "WHERE c.formMedia = 'PAPER' AND c.questId = :questId" )
                .setParameter( "questId" , questId )
                .getResultList();

        try
        {
            Iterator<String> it = ccls.iterator();
            while ( it.hasNext() )
            {
                clobStr = it.next();
                if ( clobStr != null )
                {
                    clobLen = clobStr.length();
                }
            }
        } catch ( Exception e )
        {
            Exceptions.printStackTrace( e );
        }
        return clobLen;
    }


    /**
     * Get the jpeg of the question from the CustomContentLayouts table as an image (ImageIcon)
     * @param qt  the QuestionTitle 
     * @return    the ImageIcon
     */
    public static ImageIcon getJpegFromDB( QuestionTitle qt )
    {
        byte[] imageBytes = null;
        ImageIcon ii = null;
        List<CustomContentLayouts> ccls = getCustomContentLayouts( qt );
        Iterator cclit = ccls.iterator();
        CustomContentLayouts ccl = null;
        while ( cclit.hasNext() )
        {
            ccl = (CustomContentLayouts) cclit.next();
            break; // there should only be one.
        }
        if ( ccl != null )
        {
            imageBytes = ccl.getItemLayoutJpeg();
            if ( imageBytes != null )
            {
                ii = new ImageIcon( imageBytes );
            } 
        } 
        return ii;
    }


    /**
     * Update the CustomContentLayout with the given clob at the given qt.
     *
     * @param type designerSfo, encapsulatedSfo, or itemLayout
     * @param clobStr the string of the encoded, compressed SFO
     * @param qt the question title for the given layout.
     */
    public static void updateSfoClob( SfoEnum type , String clobStr , QuestionTitle qt )
    {

        List<CustomContentLayouts> ccls = getCustomContentLayouts( qt );
        Iterator cclit = ccls.iterator();
        CustomContentLayouts ccl = null;
        while ( cclit.hasNext() )
        {
            ccl = (CustomContentLayouts) cclit.next();
            break; // there should only be one.
        }

        if ( ccl != null )
        {
            beginTransaction();
            switch ( type )
            {
                case LAYOUT:
                    ccl.setItemLayout( clobStr );
                    break;
                case DESIGNER:
                    ccl.setDesignerSfo( clobStr );
                    break;
                case ENCAPSULATED:
                    ccl.setEncapsulatedSfo( clobStr );
                    break;
                default:
                    break;
            }
            entityManager.persist( ccl );
            commitTransaction();
        }
    }


    public Clob getClob()
    {
        return clob;
    }


    public Blob getJpeg()
    {
        return jpegBlob;
    }


    public String getClobStr()
    {
        return clobStr;
    }


    /**
     * get a string which holds the contents of the given Clob
     * @param clob
     * @param clen
     * @return String representation of Clob.
     */
    public String getSfoClobString( Clob clob , long clen )
    {
        String cs = null;
        char[] chars = new char[ (int) clen + 100 ];
        try
        {
            Reader reader = clob.getCharacterStream();
            int rlen = reader.read( chars );
            cs = new String( chars );
        } catch ( SQLException | IOException e )
        {
            Exceptions.printStackTrace( e );
        }

        return cs;
    }


    /**
     *
     * return true if transaction successfully begun
     */
    public static boolean beginTransaction()
    {
        boolean transactionOpen = false;
        if ( entityManager.isOpen() )
        {
            transaction = entityManager.getTransaction();
            transaction.begin();
            transactionOpen = true;
        }
        return transactionOpen;
    }


    /**
     *
     * @return
     */
    public static boolean commitTransaction()
    {
        boolean transactionCommitted = false;
        if ( transaction.isActive() )
        {
            transaction.commit();
            transactionCommitted = true;
        }
        return transactionCommitted;
    }


    /**
     *
     * @return
     */
    public static boolean rollbackTransaction()
    {
        boolean transactionRolledback = false;
        if ( transaction.isActive() )
        {
            transaction.rollback();
            transactionRolledback = true;
        }
        return transactionRolledback;
    }

}
